from manage.db_operate import *


class Search:
    @staticmethod
    def one(target_table, target_key, target_num):
        """输入三个参数：target_table(str),target_key(str),target_num(str),当查询表为score时特殊，你应当输入两个主键码
        返回你想要查找的值"""
        keyname = ''
        if target_table.startswith('score'):
            sql_sea = f"select score from {target_table} where (snum='{target_key}' and cnum={target_num})"
        else:
            keyname_dic = {'student': 'snum', 'teacher': 'tnum', 'academic': 'anum', 'course': 'cnum',
                           'my_avg': 'snum', 'class_avg': 'classes', 'dept_avg': 'dept'}
            if target_table in keyname_dic.keys():
                keyname = keyname_dic[target_table]
            else:
                print("你输入的表名字错误")  # 替换为GUI
                return []
            sql_sea = f"select {target_key} from {target_table} where {keyname}='{target_num}'"
        try:
            cur = Db.connect.cursor()
            cur.execute(sql_sea)
            result = cur.fetchone()[0]
            cur.close()
            return result
        except:
            print("数据库错误，你输入的数据可能有误")
            return []

    @staticmethod
    def row(target_table, target_num):
        """输入两个参数，target_table(str),target_num（str），根据num来选出一行所有值，
        一个缺省参数，返回查询结果"""
        sql_sea = ''
        if target_table.startswith('score'):
            sql_sea = f"select cnum,score from {target_table} where snum='{target_num}'"
        else:
            keyname_dic = {'student': 'snum', 'teacher': 'tnum', 'academic': 'anum', 'course': 'cnum',
                           'my_avg': 'snum', 'class_avg': 'classes', 'dept_avg': 'dept'}
            if target_table in keyname_dic:
                keyname = keyname_dic[target_table]
                sql_sea = f"select * from {target_table} where {keyname}={target_num}"
            else:
                print("你输入的表名字错误")
        try:
            cur = Db.connect.cursor()
            cur.execute(sql_sea)
            result = cur.fetchall()
            if len(result) == 0:
                print("未查询到结果")
            cur.close()
            return result
        except:
            print("数据库错误，你输入的数据可能有误")
            return []

    @staticmethod
    def col(table_name, col_name):
        """查看一整个表的一列，传入参数table_name（str），col_name(str)"""
        try:
            sql_sea = f"select {col_name} from {table_name}"
            cur = Db.connect.cursor()
            cur.execute(sql_sea)
            result = cur.fetchall()
            result2 = []
            for i in result:
                result2.append(i[0])
            return result2
        except:
            print("数据库错误")
            return []

    @staticmethod
    def by_dept(table_name, dept):
        """输入成绩数据年份，和想要查询人员成绩的专业year（int），sdept（str），返回查询结果"""
        sql_sea = ''
        if table_name.startswith('score') or table_name.startswith('my_avg'):
            sql_sea = f"select {table_name}.* from {table_name} join student using (snum) where student.sdept='{dept}'"
        elif table_name == 'teacher':
            sql_sea = f"select * from teacher where tdept={dept}"
        elif table_name == 'student':
            sql_sea = f"select * from student where sdept={dept}"
        else:
            print("输入错误")
            return []
        try:
            cur = Db.connect.cursor()
            cur.execute(sql_sea)
            result = cur.fetchall()
            return result
        except:
            print("数据库错误")
            return []

    @staticmethod
    def by_class(table_name, sclass):
        """输入成绩数据年份，和想要查询人员成绩的班级year（int），sclass（int），只能应用于成绩和学生表，返回查询结果"""
        sql_sea = ''
        if table_name.startswith('score') or table_name.startswith('my_avg'):
            sql_sea = f"select {table_name}.* from {table_name} join student " \
                      f"using (snum) where student.sclass={sclass}"
        elif table_name == 'student':
            sql_sea = f"select * from student where sclass={sclass}"
        try:
            cur = Db.connect.cursor()
            cur.execute(sql_sea)
            result = cur.fetchall()
            return result
        except:
            print("数据库错误")
            return []

    @staticmethod
    def by_course(table_name, cnum):
        """按照课程号查看成绩，或者教师信息传入参数table_name(str),cnum(int)"""
        try:
            sql_sea = f"select {table_name}.* from {table_name} where {table_name}.cnum={cnum}"
            cur = Db.connect.cursor()
            cur.execute(sql_sea)
            result = cur.fetchall()
            return result
        except:
            print("数据库错误")
            return []

    @staticmethod
    def by_table(table_name):
        """查看一整个表，传入参数table_name（str）"""
        try:
            sql_sea = f"select * from {table_name}"
            cur = Db.connect.cursor()
            cur.execute(sql_sea)
            result = cur.fetchall()
            return result
        except:
            print("数据库错误")
            return []

    @staticmethod
    def for_avg(table_name, num):
        try:
            kname_dict = {'my_avg': 'snum', 'class_avg': 'classes', 'dept_avg': 'dept'}
            key_name = kname_dict[f'{table_name}']
            sql_sea = f"select * from {table_name} where {key_name}='{num}'"
            cur = Db.connect.cursor()
            cur.execute(sql_sea)
            result = cur.fetchall()
            return list(result[0])
        except:
            print("数据库错误")
            return []


class Check:
    @staticmethod
    def password(role, num, password):
        """用户登录后首先执行此函数，检查密码和用户名，输入三个参数：role（str），num（str），password（str），返回Boolean"""
        knum_dic = {'student': 'snum', 'teacher': 'tnum', 'academic': 'anum'}
        if role in knum_dic.keys():
            k_num = knum_dic[role]
        else:
            print("你应该输入：student，teacher，academic中的一个")  # 更换为GUI
            return False
        try:
            cur = Db.connect.cursor()
            sql_pas = f"select code from {role} where {k_num}='{num}'"
            cur.execute(sql_pas)
            Db.connect.commit()
            if password in list(cur.fetchone()):
                return True
            else:
                print("用户名或密码错误")  # 更换为GUI
            cur.close()
        except:
            return False

    @staticmethod
    def competence(role, tdept='', target_num=''):
        """检查用户进行操作的权限，主要适用于teacher角色，三个参数role(str),tdept(str),target_num(str)，返回Boolean"""
        if role == 'academic':
            return True
        elif role == 'teacher':
            if tdept == Search.one('student', 'sdept', target_num):
                return True
            else:
                return False


class Insert:
    @staticmethod
    def ins_sc(snum, cnum, score, year=4):
        """插入score表，三个参数snum（str），cnum（int），score（float or int）,year(int)返回Boolean"""
        if InputCheck.score(snum, cnum, score):
            try:
                cur = Db.connect.cursor()
                data_ins = (snum, cnum, score)
                sql_ins = f'insert into score{year} (snum,cnum,score) values (?,?,?)'
                cur.execute(sql_ins, data_ins)
                Db.connect.commit()
                cur.close()
                return True
            except sqlite3.IntegrityError:
                print("你输入的数据不符合约束条件(例如:学号和课程号同时与已有记录重复)")  # 更换为GUI
                return False

    @staticmethod
    def ins_co(cnum, cname, ctime, cplace):
        """插入course表，四个参数cnum（int），cname（str），ctime（int），cplace（str）返回Boolean"""
        if InputCheck.course(cnum, cname, ctime, cplace):
            try:
                cur = Db.connect.cursor()
                data_ins = (cnum, cname, ctime, cplace)
                sql_ins = 'insert into course (cnum,cname,ctime,cplace) values (?,?,?,?)'
                cur.execute(sql_ins, data_ins)
                Db.connect.commit()
                cur.close()
                return True
            except sqlite3.IntegrityError:
                print("你输入的数据不符合约束条件(例如：课程号重复)")  # 更换为GUI
                return False

    @staticmethod
    def ins_ac(anum, aname, age, sexual, code):
        """插入academic表，五个参数anum（str），aname（str），age（int），sexual（str），code（str）返回Boolean"""
        if InputCheck.academic(anum, aname, age, sexual, code):
            try:
                cur = Db.connect.cursor()
                data_ins = (anum, aname, age, sexual, code)
                sql_ins = 'insert into academic (anum,aname,age,sexual,code) values (?,?,?,?,?)'
                cur.execute(sql_ins, data_ins)
                Db.connect.commit()
                cur.close()
                return True
            except sqlite3.IntegrityError:
                print("你输入的数据不符合约束条件(例如：账号重复)")  # 更换为GUI
                return False

    @staticmethod
    def ins_st(snum, sname, sdept, sclass, sexual, code):
        """插入student表，六个参数snum（str），sname（str），sdept（str），
        sclass（int），sexual（str），code（str）返回Boolean"""
        if InputCheck.student(snum, sname, sdept, sclass, sexual, code):
            try:
                cur = Db.connect.cursor()
                data_ins = (snum, sname, sdept, sclass, sexual, code)
                sql_ins = 'insert into student (snum,sname,sdept,sclass,sexual,code) values (?,?,?,?,?,?)'
                cur.execute(sql_ins, data_ins)
                Db.connect.commit()
                cur.close()
                return True
            except sqlite3.IntegrityError:
                print("你输入的数据不符合约束条件(例如：账号重复)")  # 更换为GUI
                return False

    @staticmethod
    def ins_te(tnum, tname, tdept, cnum, age, sexual, code):
        """插入teacher表，七个参数tnum（str），tname（str），tdept（str），cnum（int），
        age（int），sexual（str）code（str）返回Boolean"""
        if InputCheck.teacher(tnum, tname, tdept, cnum, age, sexual, code):
            try:
                cur = Db.connect.cursor()
                data_ins = (tnum, tname, tdept, cnum, age, sexual, code)
                sql_ins = 'insert into teacher (tnum,tname,tdept,cnum,age,sexual,code) values (?,?,?,?,?,?,?)'
                cur.execute(sql_ins, data_ins)
                Db.connect.commit()
                cur.close()
                Db.connect.commit()
                return True
            except sqlite3.IntegrityError:
                print("你输入的数据不符合约束条件(例如：账号重复)")  # 更换为GUI
                return False

    @staticmethod
    def ins_avg(table_name, num, period1=0, period2=0, period3=0, period4=0):
        kname_dict = {'my_avg': 'snum', 'class_avg': 'classes', 'dept_avg': 'dept'}
        key_name = kname_dict[f'{table_name}']
        if InputCheck.avg(num, period1, period2, period3, period4):
            try:
                cur = Db.connect.cursor()
                data_ins = (num, period1, period2, period3, period4)
                sql_ins = f'insert into {table_name} ({key_name},period1, period2, period3, period4) values (?,?,?,?,?)'
                cur.execute(sql_ins, data_ins)
                Db.connect.commit()
                cur.close()
                Db.connect.commit()
                return True
            except:
                return False


class Delete:
    @staticmethod
    def del_row(table_name, num, num2=1):
        """如果用于score，则应当输入两个主码（数字）即：snum和cnum，其余表应该输入名称和主码返回Boolean"""
        if table_name.startswith('score'):
            sql_del = f"delete from {table_name} where (snum={num} and cnum={num2})"
        else:
            kname_dic = {'student': 'snum', 'teacher': 'tnum', 'academic': 'anum', 'course':
                'cnum', 'my_avg': 'snum', 'class_avg': 'classes', 'dept_avg': 'dept'}
            try:
                k_name = kname_dic[table_name]
            except:
                print("你输入的名称错误")  # 更换为GUI
                return False
            sql_del = f"delete from {table_name} where {k_name}='{num}'"
        try:
            cur = Db.connect.cursor()
            cur.execute(sql_del)
            cur.close()
            Db.connect.commit()
            return True
        except sqlite3.IntegrityError:
            print("你输入的数据不存在于表中")  # 更换为GUI
            return False

    @staticmethod
    def del_table(table_name):
        """输入一个表名来删除这个表,返回Boolean"""
        try:
            cur = Db.connect.cursor()
            sql_del = f"drop table {table_name}"
            cur.execute(sql_del)
            Db.connect.commit()
            cur.close()
            return True
        except sqlite3.OperationalError:
            print("你输入的表名不存在于数据库中")
            return False
        except:
            print("未知错误")
            return False


class Alter:
    @staticmethod
    def update(table_name, key_name, value, target_key, target):
        """修改表中某个值，输入五个参数：table_name（str），key_name(str),value(str or (int or float)),
        key(str),target(str or (int or float)),返回Boolean"""
        try:
            cur = Db.connect.cursor()
            if (isinstance(value, int) or isinstance(value, float)) and not table_name.startswith('score'):
                sql_alter = f"update {table_name} set {key_name}={value} where {target_key}={target}"
            elif (isinstance(value, int) or isinstance(value, float)) and table_name.startswith('score'):
                sql_alter = f"update {table_name} set {key_name}={value} where snum={target_key} and cnum={target}"
            else:
                sql_alter = f"update {table_name} set {key_name}='{value}' where {target_key}='{target}'"
            cur.execute(sql_alter)
            cur.close()
            Db.connect.commit()
            return True
        except:
            print("输入的数据错误")
            return False


class InputCheck:
    @staticmethod
    def score(para1, para2, para3):
        """检查输入是否符合对应表的结构要求,返回Boolean"""
        if (isinstance(para1, str)) and (isinstance(para2, int)) and \
                (isinstance(para3, float) or isinstance(para3, float)):
            return True
        else:
            print("你输入了错误的数据类型，应为：str,int,float")
            return False

    @staticmethod
    def course(para1, para2, para3, para4):
        """检查输入是否符合对应表的结构要求,返回Boolean"""
        if (isinstance(para1, int)) and (isinstance(para2, str)) and (isinstance(para3, int)) \
                and (isinstance(para4, str)):
            return True
        else:
            print("你输入了错误的数据类型，应为:int,str,int,str")
            return False

    @staticmethod
    def academic(para1, para2, para3, para4, para5):
        """检查输入是否符合对应表的结构要求,返回Boolean"""
        if (isinstance(para1, str)) and (isinstance(para2, str)) and (isinstance(para3, int)) \
                and (isinstance(para4, str)) and (isinstance(para5, str)):
            return True
        else:
            print("你输入了错误的数据类型，应为:str,str,int,str,str")
            return False

    @staticmethod
    def student(para1, para2, para3, para4, para5, para6):
        """检查输入是否符合对应表的结构要求,返回Boolean"""
        if (isinstance(para1, str)) and (isinstance(para2, str)) and (isinstance(para3, str)) \
                and (isinstance(para4, int)) and (isinstance(para5, str)) and (isinstance(para6, str)):
            return True
        else:
            print("你输入了错误的数据类型，应为:str,str,str,int,str,str")
            return False

    @staticmethod
    def teacher(para1, para2, para3, para4, para5, para6, para7):
        """检查输入是否符合对应表的结构要求,返回Boolean"""
        if (isinstance(para1, str)) and (isinstance(para2, str)) and (isinstance(para3, str)) \
                and (isinstance(para4, int)) and (isinstance(para5, int)) and (isinstance(para6, str)) \
                and (isinstance(para7, str)):
            return True
        else:
            print("你输入了错误的数据类型，应为:str,str,str,int,int,str,str")
            return False

    @staticmethod
    def avg(para1, para2, para3, para4, para5):
        if (isinstance(para1, str) or isinstance(para1, int)) and (isinstance(para2, float)) and isinstance(para3,
                                                                                                            float) \
                and isinstance(para4, float) and isinstance(para5, float):
            return True
        else:
            print("你输入了错误的数据类型")
            return False
